﻿
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Xml;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using EQT.Model;

namespace EQT.Dal
{
    public partial class DaTuser
    {
        /// <summary>
        /// 登入系统，状态2标识该用户可用
        /// </summary>
        private const string SQL_LOGIN = "SELECT COUNT(*) FROM TUser WHERE  UID=@UID AND PWD=@PWD AND Status=2";
        
        /// <summary>
        /// 查询用户与员工的对应信息
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public DataSet GetUser_Emp_Info(string deptid)
        {
            string strGetUser_Emp_Info = "SELECT * FROM V_User_Emploree WHERE Dept_ID"+ (deptid==""?" is null":"=@Dept_ID ");
            try
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strGetUser_Emp_Info);

                //设置参数:Dept_ID	
                if (!string.IsNullOrEmpty(deptid))
                {
                    db.AddInParameter(dbCommand, "Dept_ID", DbType.String, deptid == "" ? null : deptid);
                }
                return db.ExecuteDataSet(dbCommand);
            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            return new DataSet();
        }

        /// <summary>
        /// 查询全部有效用户.
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllAvailableUsers()
        {
            string strGetUser_Emp_Info = "SELECT * FROM Tuser WHERE status=2 ";
            try
            {
                return db.ExecuteDataSet(CommandType.Text,strGetUser_Emp_Info);
            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            return new DataSet();
        }

        /// <summary>
        /// 查询某个组的所有用户
        /// </summary>
        /// <param name="deptid">组编号</param>
        /// <returns></returns>
        public DataSet GetAllAvailableUsers(string groupid)
        {
            string strGetUser_Emp_Info = "SELECT UID, Emp_ID, Display_Name FROM TUser WHERE (UID IN (SELECT UID FROM User_Role WHERE (Role_ID = '" + groupid + "')))";
            //string strGetUser_Emp_Info = "SELECT * FROM V_User_Emploree WHERE status=2 and dept_id='" + groupid + "'";
            try
            {
                return db.ExecuteDataSet(CommandType.Text, strGetUser_Emp_Info);
            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            return new DataSet();
        }


        /// <summary>
        /// 登入系统
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="user_pwd"></param>
        /// <returns></returns>
        public bool Login(string user_id, string user_pwd)
        {
            int rc = 0;
            DbCommand cmd= db.GetSqlStringCommand(SQL_LOGIN);
            db.AddInParameter(cmd, "UID", DbType.String, user_id);
            db.AddInParameter(cmd, "PWD", DbType.String, user_pwd);
            object obj= db.ExecuteScalar(cmd);
            if (obj != null && obj != DBNull.Value)
            {
                rc = Convert.ToInt32(obj);                
            }
            return rc > 0;

        }

        /// <summary>
        /// 用户上下文
        /// </summary>
        /// <param name="user_id">用户ID</param>
        /// <returns>用户上下文对象</returns>
        public UserContext GetUserContext(string user_id)
        {
            DaDept dadept=new DaDept(db);
            DaEmployee deemp = new DaEmployee(db);
            DaUserRole daur = new DaUserRole(db);
            DaTrole datr = new DaTrole(db);

            MoTuser tuser = new MoTuser();
            tuser.Uid = user_id;             
            if (this.GetEntityEx(ref tuser))
            {
                UserContext uc = new UserContext();
                uc.TUser = tuser;

                if (!String.IsNullOrEmpty(tuser.EmpId))
                {
                    MoEmployee emp = new MoEmployee();
                    emp.EmpId = tuser.EmpId;
                    deemp.GetEntityEx(ref emp);
                    uc.Emp = emp;

                    MoDept dept = new MoDept();
                    dept.DeptId = uc.Emp.DeptId;
                    dadept.GetEntityEx(ref dept);
                    uc.Dept = dept;
                }
                
                
                //目前支持1个用户1个角色
                IList<MoUserRole> lur= daur.GetEntities(String.Format(" WHERE UID='{0}'", tuser.Uid));
                if (lur != null && lur.Count > 0)
                {
                    uc.UserRole = lur[0];                    
                    MoTrole role = new MoTrole();
                    role.RoleId = uc.RoleID;
                    datr.GetEntityEx(ref role);
                    uc.TRole = role;
                }
                return uc;
                
            }
            return null;
        }

        public int CreateUser(MoTuser mod,string roleID)
        {
            int rc = 0;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {
                        this.Add(mod, trans);
                        DaUserRole dauserrole = new DaUserRole(db);
                        dauserrole.Add(new MoUserRole(mod.Uid, roleID), trans);
                        trans.Commit();
                    }
                    cnn.Close();
                    return 1;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        public int UpdateUser(MoTuser mod, string roleID,string oldRoleID)
        {
            //string SQL_EXIST = "select * from user_role where  UID='" + mod.Uid + "' AND  Role_ID='" + roleID + "'";

            string SQL_UPDATE = "UPDATE User_Role SET Role_ID='"+roleID+"' WHERE UID='"+mod.Uid+"' AND  Role_ID='"+oldRoleID +"'";
            Dal.DaUserRole dalrol = new DaUserRole(db);

            int rc = 0;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {
                        if (string.IsNullOrEmpty(oldRoleID))
                        {
                            dalrol.Add(new MoUserRole(mod.Uid, roleID), trans);
                        }
                        else
                        {
                            db.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE);
                        }
                        this.Update(mod, trans);
                        trans.Commit();
                    }
                    cnn.Close();
                    return 1;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        public int DeleteUser(string uid, string strRoleID)
        {
            DaUserRole dauserrole = new DaUserRole(db);

            int rc = 0;
            using (DbConnection cnn = db.CreateConnection())
            {
                DbTransaction trans = null;
                try
                {
                    cnn.Open();
                    using (trans = cnn.BeginTransaction())
                    {

                        MoUserRole ur = new MoUserRole();
                        ur.RoleId = strRoleID;
                        ur.Uid = uid;
                        dauserrole.Delete(ur, trans);
                        this.DeleteByWhereClause(String.Format("UID='{0}'", uid), trans);
                        trans.Commit();
                    }
                    cnn.Close();
                    return 1;
                }
                catch (DbException ex)
                {
                    if (trans != null) trans.Rollback();
                    HandleDbException(ex);
                }
            }
            return rc;
        }

        public DataSet Query()
        { 
            string sql="SELECT t.*, u.Role_ID AS Role_ID, r.Role_Name AS Role_Name"+
                        " FROM TUser t LEFT OUTER JOIN"+
                        " User_Role u ON t.UID = u.UID LEFT OUTER JOIN"+
                        " TRole r ON u.Role_ID = r.Role_ID where t.status<>-1";
            try
            {
                return db.ExecuteDataSet(CommandType.Text, sql);

            }
            catch (DbException ex)
            {
                HandleDbException(ex);
            }
            return new DataSet();
        }

        /// <summary>
        /// 删除签名文件。
        /// </summary>
        /// <param name="strUID">用户编号</param>
        /// <returns></returns>
        public bool RemoveSignaturePic(string strUID)
        {
            DaTfiles dalFile = new DaTfiles(db);
            int rc = 0;
            using (DbConnection conn = db.CreateConnection())
            {
                try
                {
                    conn.Open();
                    using (DbTransaction trans = conn.BeginTransaction())
                    {
                        string fileid = string.Empty;
                        MoTuser moduser = this.GetEntities(" where uid='" + strUID + "'")[0];
                        moduser.Signaturepic = "";
                        rc+=this.Update(moduser, trans);

                        string sqlDeleteRef = "delete from Ref_File where file_id='" + fileid + "'";
                        rc += db.ExecuteNonQuery(trans, CommandType.Text, sqlDeleteRef);
                        rc += dalFile.DeleteByWhereClause(String.Format(" WHERE FILE_ID='{0}'", fileid), trans);
                        trans.Commit();
                    }
                    conn.Close();
                    return true;
                }
                catch (DbException de)
                {
                    throw de;
                }
            }
        }

        /// <summary>
        /// 检查用户是否具有某个权限
        /// </summary>
        const string SQL_CheckUserPrivilege = "SELECT COUNT(*) FROM V_USER_PRIVILEGE WHERE UID=@UID AND FUNC_ID=@FUNC_ID";
        /// <summary>
        /// 检查一个用户是否具有某个权限
        /// </summary>
        /// <param name="user_id"></param>
        /// <param name="privilegeId"></param>
        /// <returns></returns>
        public bool CheckUserPrivilege(string uid, string privilegeId)
        {
            DbCommand cmd = db.GetSqlStringCommand(SQL_CheckUserPrivilege);
            db.AddInParameter(cmd, "UID", DbType.String, uid);
            db.AddInParameter(cmd, "FUNC_ID", DbType.String, privilegeId);
            return (int)db.ExecuteScalar(cmd) > 0;
        }
    }
}

